package co.edu.eam.iso.model.dao;

import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import co.edu.eam.iso.enums.CharacteristicEnum;
import co.edu.eam.iso.enums.ScaleEnum;
import co.edu.eam.iso.enums.SubCharacteristicEnum;
import co.edu.eam.iso.logger.Logger;
import co.edu.eam.iso.model.Characteristic;
import co.edu.eam.iso.model.Evaluation;
import co.edu.eam.iso.model.Metric;
import co.edu.eam.iso.model.SubCharacteristic;
import co.edu.eam.iso.util.BigDecimalUtil;

/**
 * @author Giovanny Ramirez
 * @version 2.0
 */
public class EvaluationDAO {
	
	private static EvaluationDAO instance = null; 
	
	public static EvaluationDAO getInstance() {
		if (instance == null) {
			instance = new EvaluationDAO();
		}
		return instance;
	}
	
	public int saveEvaluation(int evaluationId, String evaluationName,
			Map<CharacteristicEnum, Characteristic> characteristics)
			throws Exception {
		PreparedStatement preparedStatement = null;
		
		try {
			final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
			String date = SIMPLE_DATE_FORMAT.format(Calendar.getInstance().getTime());

			Connection connection = DataSource.getConnection();
			if (evaluationId == 0) {
				preparedStatement = connection.prepareStatement("select max(id)+1 from evaluations;");
				preparedStatement.execute();
				int id = preparedStatement.getResultSet().getInt(1);
				evaluationId = id > 0 ? id : 1;
				preparedStatement.close();
				
				preparedStatement = connection.prepareStatement("insert into evaluations (id, name, date) values (?, ?, ?);");
				preparedStatement.setInt(1, evaluationId);
				preparedStatement.setString(2, evaluationName);
				preparedStatement.setString(3, date);
				preparedStatement.execute();
				preparedStatement.close();
				
				saveEvaluationDetail(connection, preparedStatement, characteristics, evaluationId);
			} else {
				preparedStatement = connection.prepareStatement("update evaluations set name=?, date=? where id=?");
				preparedStatement.setString(1, evaluationName);
				preparedStatement.setString(2, date);
				preparedStatement.setInt(3, evaluationId);
				preparedStatement.execute();
				preparedStatement.close();
				
				preparedStatement = connection.prepareStatement("delete from evaluation_chrtcs where id_evaluation=?");
				preparedStatement.setInt(1, evaluationId);
				preparedStatement.execute();
				preparedStatement.close();
				
				preparedStatement = connection.prepareStatement("delete from evaluation_subchrtcs where id_evaluation=?");
				preparedStatement.setInt(1, evaluationId);
				preparedStatement.execute();
				preparedStatement.close();
				
				preparedStatement = connection.prepareStatement("delete from evaluation_metrics where id_evaluation=?");
				preparedStatement.setInt(1, evaluationId);
				preparedStatement.execute();
				preparedStatement.close();

				saveEvaluationDetail(connection, preparedStatement, characteristics, evaluationId);
			}			
		} catch (Exception e) {
			Logger.getInstance().registrarLog(e);
			throw e;
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				
				DataSource.getConnection().close();
			} catch (SQLException e) {
				Logger.getInstance().registrarLog(e);
			}
		}
		
		return evaluationId;
	}
	
	private void saveEvaluationDetail(Connection connection, PreparedStatement preparedStatement,
			Map<CharacteristicEnum, Characteristic> characteristics,
			int evaluationId) throws Exception {
		
		for (Entry<CharacteristicEnum, Characteristic> characteristic : characteristics
				.entrySet()) {
			preparedStatement = connection.prepareStatement("insert into evaluation_chrtcs (id_evaluation, id_characteristic, weight) values (?, ?, ?);");
			preparedStatement.setInt(1, evaluationId);
			preparedStatement.setInt(2, characteristic.getKey().getId());
			preparedStatement.setDouble(3, characteristic.getValue().getWeight().doubleValue());
			preparedStatement.execute();
			preparedStatement.close();
						
			for (Entry<SubCharacteristicEnum, SubCharacteristic> subCharacteristic : characteristic
					.getValue().getSubcharacteristics().entrySet()) {
				preparedStatement = connection.prepareStatement("insert into evaluation_subchrtcs (id_evaluation, id_subcharacteristic, weight) values (?, ?, ?);");
				preparedStatement.setInt(1, evaluationId);
				preparedStatement.setInt(2, subCharacteristic.getKey().getId());
				preparedStatement.setDouble(3, subCharacteristic.getValue().getWeight().doubleValue());
				preparedStatement.execute();
				preparedStatement.close();

				for (Metric metric : subCharacteristic.getValue().getMetrics()) {
					int id = metric.getId();
					
					if (id == 0) {
						preparedStatement = connection.prepareStatement("select max(id)+1 from metrics;");
						preparedStatement.execute();
						id = preparedStatement.getResultSet().getInt(1);
						preparedStatement.close();
						
						preparedStatement = connection.prepareStatement(
								"insert into metrics (id, description, id_subcharacteristic) values (?, ?, ?);");
						preparedStatement.setInt(1, id);
						preparedStatement.setString(2, metric.getDescription());
						preparedStatement.setInt(3, metric.getSubCharacteristicEnum().getId());
						preparedStatement.executeUpdate();
						preparedStatement.close();
					}
										
					preparedStatement = connection.prepareStatement("insert into evaluation_metrics (id_evaluation, id_metric, id_scale) values (?, ?, ?);");
					preparedStatement.setInt(1, evaluationId);
					preparedStatement.setInt(2, id);
					preparedStatement.setInt(3, metric.getScale().getId());
					preparedStatement.execute();
				}
				
				preparedStatement.close();
			}
		}		
	}
	
	public List<Evaluation> searchEvaluations() {
		List<Evaluation> evaluations = new ArrayList<Evaluation>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			preparedStatement = DataSource.getConnection().prepareStatement("select e.id, e.name, e.date from evaluations e;");
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Evaluation evaluation = new Evaluation();
				evaluation.setId(resultSet.getInt(1));
				evaluation.setName(resultSet.getString(2));
				evaluation.setDate(resultSet.getString(3));
				evaluations.add(evaluation);
			}
		} catch (SQLException e) {
			Logger.getInstance().registrarLog(e);
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				
				if (resultSet != null) {
					resultSet.close();
				}
				
				DataSource.getConnection().close();
			} catch (SQLException e) {
				Logger.getInstance().registrarLog(e);
			}
		}
		
		return evaluations;
	}
	
	public Map<CharacteristicEnum, Characteristic> searchEvaluationDetail(int id)
			throws Exception {
		Map<CharacteristicEnum, Characteristic> detail = new HashMap<CharacteristicEnum, Characteristic>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			String sql = "select m.id, m.description, em.id_scale, es.id_subcharacteristic, "
					+ "es.weight, ec.id_characteristic, ec.weight "
					+ "from evaluation_chrtcs ec "
					+ "join sub_characteristics s on ec.id_characteristic=s.id_characteristic "
					+ "join evaluation_subchrtcs es on es.id_subcharacteristic=s.id "
					+ "left join evaluation_metrics em on (em.id_metric=m.id and (em.id_evaluation is null or em.id_evaluation=?)) "
					+ "left join metrics m on m.id_subcharacteristic=es.id_subcharacteristic "
					+ "where ec.id_evaluation=? and es.id_evaluation=?;";
			
			preparedStatement = DataSource.getConnection().prepareStatement(sql);					
			preparedStatement.setInt(1, id);
			preparedStatement.setInt(2, id);
			preparedStatement.setInt(3, id);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				boolean addMetric = resultSet.getInt(1) > 0;
				CharacteristicEnum characteristicEnum = CharacteristicEnum.getById(resultSet.getInt(6));
				SubCharacteristicEnum subCharacteristicEnum = SubCharacteristicEnum.getById(resultSet.getInt(4));
				
				if (detail.get(characteristicEnum) != null) {
					if (detail.get(characteristicEnum).getSubcharacteristics()
							.get(subCharacteristicEnum) != null) {
						if (addMetric) {
							Metric metric = new Metric();
							metric.setId(resultSet.getInt(1));
							metric.setDescription(resultSet.getString(2));
							metric.setScale(ScaleEnum.getById(resultSet.getInt(3)));
							metric.setSubCharacteristicEnum(subCharacteristicEnum);
							detail.get(characteristicEnum).getSubcharacteristics()
									.get(subCharacteristicEnum).getMetrics().add(metric);
						}
					} else {
						SubCharacteristic subCharacteristic = new SubCharacteristic();
						subCharacteristic.setSubCharacteristicEnum(subCharacteristicEnum);
						subCharacteristic.setMetrics(new ArrayList<Metric>());
						subCharacteristic.setWeight(BigDecimalUtil.round(
								BigDecimalUtil.wrap(resultSet.getDouble(5)), 2,
								RoundingMode.HALF_UP));

						if (addMetric) {
							Metric metric = new Metric();
							metric.setId(resultSet.getInt(1));
							metric.setDescription(resultSet.getString(2));
							metric.setScale(ScaleEnum.getById(resultSet.getInt(3)));
							metric.setSubCharacteristicEnum(subCharacteristicEnum);
							subCharacteristic.getMetrics().add(metric);
						}

						detail.get(characteristicEnum).getSubcharacteristics().put(subCharacteristicEnum, subCharacteristic);
					}
				} else {					
					Characteristic characteristic = new Characteristic();
					characteristic.setCharacteristicEnum(characteristicEnum);
					characteristic.setWeight(BigDecimalUtil.round(
							BigDecimalUtil.wrap(resultSet.getDouble(7)), 2,
							RoundingMode.HALF_UP));
					
					SubCharacteristic subCharacteristic = new SubCharacteristic();
					subCharacteristic.setSubCharacteristicEnum(subCharacteristicEnum);
					subCharacteristic.setMetrics(new ArrayList<Metric>());
					subCharacteristic.setWeight(BigDecimalUtil.round(
							BigDecimalUtil.wrap(resultSet.getDouble(5)), 2,
							RoundingMode.HALF_UP));
					
					if (addMetric) {
						Metric metric = new Metric();
						metric.setId(resultSet.getInt(1));
						metric.setDescription(resultSet.getString(2));
						metric.setScale(ScaleEnum.getById(resultSet.getInt(3)));
						metric.setSubCharacteristicEnum(subCharacteristicEnum);
						subCharacteristic.getMetrics().add(metric);
					}
					
					characteristic.setSubcharacteristics(new HashMap<SubCharacteristicEnum, SubCharacteristic>());
					characteristic.getSubcharacteristics().put(subCharacteristicEnum, subCharacteristic);
					
					detail.put(characteristicEnum, characteristic);
				}
			}	
		} catch (Exception e) {
			Logger.getInstance().registrarLog(e);
			throw e;
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				DataSource.getConnection().close();
			} catch (SQLException e) {
				Logger.getInstance().registrarLog(e);
			}
		}
		
		return detail;
	}

	public void deleteEvaluation(int id) {
		PreparedStatement preparedStatement = null;
		
		try {
			Connection connection = DataSource.getConnection();
			
			preparedStatement = connection.prepareStatement("delete from evaluations where id =?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
			preparedStatement.close();
			
			preparedStatement = connection.prepareStatement("delete from evaluation_chrtcs where id_evaluation=?");
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			preparedStatement.close();
			
			preparedStatement = connection.prepareStatement("delete from evaluation_subchrtcs where id_evaluation=?");
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			preparedStatement.close();
			
			preparedStatement = connection.prepareStatement("delete from evaluation_metrics where id_evaluation=?");
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			preparedStatement.close();			
		} catch (SQLException e) {
			Logger.getInstance().registrarLog(e);
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				
				DataSource.getConnection().close();
			} catch (SQLException e) {
				Logger.getInstance().registrarLog(e);
			}
		}
	}	
}
